local function rows(conn, cmd)
    local cur = conn:execute(cmd)
    return function ()
      return cur:fetch()
    end    
end 

local function main(conn, msg)
    local type = msg['behavior']

    if type == "get" then
        local condition = msg["condition"] or ""
        local sql = "SELECT `id`,`from_user`, `to_user`, `type` FROM `task`"
        if condition ~= "" then
            sql = sql..condition
        end
        local data = {}
        for id,from_user,to_user,type in rows(conn, sql) do
            local span = {}
            span.from_user = from_user
            span.to_user = to_user
            span.type = type
            span.id = id
            data[#data+1] = span
        end    
    return data    
    elseif type == "add" then
        local sql = string.format("INSERT INTO `task`(`from_user`, `to_user`, `type`) VALUES ('%s', '%s', %s);", msg['from_user'],msg['to_user'],msg['type'])
        local affected_rows = conn:execute(sql)
        return affected_rows > 0
    elseif type == "update" then   
        local sql = string.format("UPDATE `task` SET `from_user`='%s', `to_user`='%s', `type`=%s WHERE id = %s", msg['from_user'],msg['to_user'],msg['type'],msg['id'])
        local affected_rows = conn:execute(sql)
        return affected_rows > 0

    elseif type == "ranking" then
        local sql = string.format("SELECT task.from_user,task.`to_user`, snap_user.`level` FROM task LEFT JOIN snap_user  ON task.`from_user`= snap_user.`name` WHERE task.`from_user`='%s' AND task.`type`=1 ORDER BY snap_user.`level` DESC",msg['from_user'])
        local data = {}
        for id,from_user,to_user,level in rows(conn, sql) do
            local span = {}
            span.from_user_id = from_user
            span.to_user_id = to_user
            span.level = level
            data[#data+1] = span
        end    
        return data    

    elseif type == "rankinglist" then
  
       local sql = "SELECT snap_user.`name`, rankinglist.`ranking`,snap_user.`level`, snap_user.`stage_level`,snap_user.`nickname` FROM rankinglist LEFT JOIN snap_user ON  rankinglist.`name`= snap_user.`name`"
       if msg["fromRank"] and msg["toRank"] then
        --  local where_sql = string.format(" WHERE rankinglist.`ranking`>= %s and rankinglist.`ranking` <= %s", msg["fromRank"],msg["toRank"])
        --  sql = sql..where_sql
       end 
       local orderby_sql = " ORDER BY `ranking` ASC"
       sql = sql..orderby_sql
       local data = {}
       local inranking = 0
    
       for name, ranking, level,stage_level in rows(conn,sql) do
      
        local span = {}
        if math.floor(ranking) >= math.floor(msg["fromRank"]) and  math.floor(ranking) <= math.floor(msg["toRank"]) then
        span.name = name
        span.ranking = ranking
        level = level-1000
        span.level = level
        span.stage_level = stage_level
        data[#data+1] = span
        end    

        if math.floor(name) == math.floor(msg["name"]) then
          inranking = ranking
        end    
        
       end

       local commondata = {}
       commondata.data = data
       commondata.inranking = inranking
    
        return commondata

    end    

end

return main    